ORACLE自动扩展表空间添加数据文件

以下均在sys用户以sysdba登录后执行，否则会因为权限问题报错

1.建wifi.dba_data_files_log记录表
先判断这张表是否已经存在，如果不存在，则建表（原dba_data_files的字段加上time,sql两个字段）

DECLARE
  STR VARCHAR2(200);
BEGIN
  SELECT COUNT(1)
    INTO STR
    FROM DBA_TAB_COLUMNS
   WHERE TABLE_NAME = 'DBA_DATA_FILES_LOG'
     AND OWNER = 'WIFI';
  IF STR = 0 THEN
    execute immediate 'create table WIFI.DBA_DATA_FILES_LOG
(
  file_name       VARCHAR2(513),
  file_id         NUMBER,
  tablespace_name VARCHAR2(30),
  bytes           NUMBER,
  blocks          NUMBER,
  status          VARCHAR2(9),
  relative_fno    NUMBER,
  autoextensible  VARCHAR2(3),
  maxbytes        NUMBER,
  maxblocks       NUMBER,
  increment_by    NUMBER,
  user_bytes      NUMBER,
  user_blocks     NUMBER,
  online_status   VARCHAR2(7),
  time            DATE default sysdate,
  sql             VARCHAR2(4000)
)
tablespace TBS_WIFI
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  ) ';
  END IF;
END;
/

2.备份以前的数据文件信息
先判断WIFI.DBA_DATA_FILES_LOG表是否没有记录，如果没有记录则证明刚建立还未备份以前数据，先备份

DECLARE
  num number(20);
BEGIN
  SELECT COUNT(1) INTO num FROM WIFI.DBA_DATA_FILES_LOG;
  IF num = 0 THEN
    insert into wifi.dba_data_files_log
      (file_name,
       file_id,
       tablespace_name,
       bytes,
       blocks,
       status,
       relative_fno,
       autoextensible,
       maxbytes,
       maxblocks,
       increment_by,
       user_bytes,
       user_blocks,
       online_status,
       time,
       sql)
      select file_name,
             file_id,
             tablespace_name,
             bytes,
             blocks,
             status,
             relative_fno,
             autoextensible,
             maxbytes,
             maxblocks,
             increment_by,
             user_bytes,
             user_blocks,
             online_status,
             sysdate,
             ''
        from dba_data_files;
    commit;
  END IF;
END;
/

3.显示授权给WIFI用户

grant alter database to WIFI;
grant alter tablespace to WIFI;
grant select on dba_data_files to WIFI;
grant select on dba_free_space to WIFI;

4.创建扩展表空间的存储过程

create or replace procedure wifi.DBA_ADD_TABLESPACE is
begin
  declare
    v_maxbytes       number(20);
    --v_flag           number(10);
    v_sql            varchar2(4000);
    v_filename       varchar2(512);
    v_tablespacename varchar2(200);
    v_autoextensible varchar2(10);
    cursor c_job is
      select a.tablespace_name tsname
      --,round((a.bytes_alloc - nvl(b.bytes_free, 0)) / maxbytes * 100) pct  --使用百分比
        from (select f.tablespace_name,
                     sum(f.bytes) bytes_alloc,
                     sum(decode(f.autoextensible,
                                'YES',
                                f.maxbytes,
                                'NO',
                                f.bytes)) maxbytes
                from dba_data_files f
               group by tablespace_name) a,
             (select f.tablespace_name, sum(f.bytes) bytes_free
                from dba_free_space f
               group by tablespace_name) b
       where a.tablespace_name = b.tablespace_name(+)
         and round((a.bytes_alloc - nvl(b.bytes_free, 0)) / maxbytes * 100) > 85
         and (a.tablespace_name in
             ('TBSIDX_WIFI_TERMINAL_TRACE_LOG',
               'TBS_IDX',
               'TBS_WIFI',
               'TBSIDX_WIFI_HOTSPOT_TRACE_LOG',
               'TBSIDX_TERMINAL_NETLOG') or
             a.tablespace_name like 'TBS_TERMINAL_TRACE_LOG%' or
             a.tablespace_name like 'TBS_TERMINAL_INFO%' or
             a.tablespace_name like 'TBS_HOTSPOT_TRACE_LOG%' or
             a.tablespace_name like 'TBS_TERMINAL_NETLOG%' or
             a.tablespace_name like 'TBS_WIFI_TERMINAL%');
  begin
    open c_job;
    loop
      fetch c_job
        into v_tablespacename;
      exit when c_job%notfound;

      select file_name, tablespace_name, autoextensible, maxbytes
        into v_filename, v_tablespacename, v_autoextensible, v_maxbytes
        from (select rownum num,
                     file_name,
                     tablespace_name,
                     autoextensible,
                     maxbytes
                from dba_data_files
               where tablespace_name = v_tablespacename
                 and instr(UPPER(file_name), '_ADDTIME.DBF') = 0
               order by file_id)
       where num = 1;

      if v_maxbytes < 29205763686 then
        v_sql := 'alter database datafile ''' || v_filename ||
                 '''autoextend on next 500m maxsize unlimited';
        execute immediate v_sql;
      else
        v_sql := 'alter database datafile ''' || v_filename ||
                 '''autoextend on next 500m maxsize unlimited';
        execute immediate v_sql;
        --插入日志表中，此处会修改两次，一次修改数据文件，一次添加数据文件
        insert into wifi.dba_data_files_log
          (file_name, tablespace_name, autoextensible, sql)
        values
          (v_filename, v_tablespacename, v_autoextensible, v_sql);

        v_sql := 'alter tablespace ' || v_tablespacename ||
                 ' add datafile ''' ||
                 replace(v_filename,
                         '.dbf',
                         '_' ||
                         round((sysdate -
                               to_date('19700101080000', 'yyyymmddhh24miss')) * 24 * 3600) ||
                         '_addtime.dbf') ||
                 ''' size 1G autoextend on next 500m maxsize unlimited';
        execute immediate v_sql;
      end if;
      --插入日志表中
      insert into wifi.dba_data_files_log
        (file_name, tablespace_name, autoextensible, sql)
      values
        (v_filename, v_tablespacename, v_autoextensible, v_sql);
      commit;
    end loop;
    close c_job;
  end;
end;
/

5.创建JOB

DECLARE
  num number(20);
  job number;
BEGIN
  select count(1)
    into num
    from dba_jobs
   where what = 'wifi.dba_add_tablespace;';
  IF num = 0 THEN
    sys.dbms_job.submit(job       => job,
                        what      => 'wifi.dba_add_tablespace;',
                        next_date => to_date('31-05-2016 01:00:00',
                                             'dd-mm-yyyy hh24:mi:ss'),
                        interval  => 'TRUNC(sysdate) + 1 +1/ (24)');
    commit;
  END IF;
END;
/